package bysj.dao;

import bysj.domain.Department;
import bysj.domain.StudentClass;
import bysj.service.DepartmentService;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class StudentClassDao {
    private static StudentClassDao studentClassDao = null;

    private StudentClassDao() {
    }

    public static StudentClassDao getInstance() {
        if(studentClassDao ==null){
            studentClassDao = new StudentClassDao();
        }
        return studentClassDao;
    }

    public Collection<StudentClass> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        Collection<StudentClass> desiredTeachers = new HashSet<StudentClass>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM studentClass");
        int totalNum = CommonDao.getInstance().count(connection,"studentClass");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        if(pagination!=null){
            String limit= pagination.toLimitClause(totalNum)+" ";
            sql_select.append(limit);
        }
        System.out.println(sql_select.toString());
        //获得连接对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行SQL查询语句并获得结果集对象
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            StudentClass studentClass = null;
            int id = resultSet.getInt("id");
            //为school对象的description赋值
            String description = resultSet.getString("description");
            //为school对象的no赋值
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            int department_id = resultSet.getInt("department_id");
            Department department = DepartmentService.getInstance().find(department_id);
            studentClass = new StudentClass(id,description,no,remarks,department);
            //将school增加到schools集合
            desiredTeachers.add(studentClass);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, preparedStatement);
        return desiredTeachers;
    }

    public StudentClass find(int id,Connection connection) throws SQLException {
        StudentClass studentClass = null;
        //创建sql语句
        String studentClass_sql = "SELECT * FROM studentClass WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt= connection.prepareStatement(studentClass_sql);
        //为预编译对象赋值
        pstmt.setInt(1,id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        if (resultSet.next()){
            //int id = resultSet.getInt(1);
            //为school对象的description赋值
            String description = resultSet.getString(2);
            //为school对象的no赋值
            String no = resultSet.getString(3);
            String remarks = resultSet.getString(4);
            int department_id = resultSet.getInt(5);
            Department department = DepartmentService.getInstance().find(department_id);
            studentClass = new StudentClass(id,description,no,remarks,department);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt);
        return studentClass;
    }
}
